﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Reflection;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading;
using Xant.Querier.Core;
using Xant.Querier.Tester.Model;

namespace Xant.Querier.Tester
{
    public class SQLiteHelper:IDisposable
    {
        private static readonly object obj = new object();

        private SQLiteConnection connection;
        public SQLiteHelper(SQLiteConnection conn)
        {
            this.connection = conn;
            if (connection.State == ConnectionState.Closed)
            {
                connection.Open();
            }
        }

        public SQLiteHelper(string connectionString)
        {
            connection = new SQLiteConnection(connectionString);
            connection.Open();
        }

        public void RemoveAllTables()
        {
            var sb = new StringBuilder();
            var reader = Read("SELECT tbl_name FROM sqlite_master where type='table'");
            while (reader.Read())
            {
                sb.AppendFormat("drop table {0};", reader.GetString(0));
            }
            ExecuteNonQuery(sb.ToString());
        }

        public bool TableExisted(string tableName)
        {
            var cmd = connection.CreateCommand();
            cmd.CommandText = string.Format("SELECT COUNT(*) FROM sqlite_master where type='table' and name='{0}'",
                tableName);

            Monitor.Enter(obj);
            try
            {
                var count = (long) cmd.ExecuteScalar();
                return count > 0;
            }
            finally
            {
                Monitor.Exit(obj);
            }
        }

        public string GenFieldNameForParentKey(PropertyInfo piParentKey)
        {
            var fieldName = piParentKey.Name;
            if (fieldName.Equals("id", StringComparison.InvariantCultureIgnoreCase))
            {
                fieldName = piParentKey.DeclaringType.Name + "Id";
            }
            return fieldName;
        }

        public void CreateTable(Type entityType, string tableName, PropertyInfo piParentKey)
        {
            if (TableExisted(tableName))
            {
                return;
            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat("create table {0}(", tableName);
            if (piParentKey != null)
            {
                sb.AppendFormat("{0} {1}, ", GenFieldNameForParentKey(piParentKey), piParentKey.PropertyType.GetSqlType());
            }
            var properties = entityType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            var pkProperty =
                properties.Where(p => p.Name.EndsWith("id", StringComparison.InvariantCultureIgnoreCase)).First();
            foreach (PropertyInfo property in properties)
            {
                var propertyType = property.PropertyType;
                if (property.PropertyType.IsSubclassOf(typeof(ValueObject)))
                {
                    foreach (var pi in property.PropertyType.GetProperties())
                    {
                        sb.AppendFormat("{0}_{1} {2}, ", property.Name, pi.Name, pi.PropertyType.GetSqlType());
                    }
                    continue;
                }
                else if (propertyType.IsSubclassOf(typeof (Entity)))
                {
                    CreateTable(property.PropertyType, property.Name);
                    sb.AppendFormat("{0}Id GUID, ", property.Name);
                    continue;
                }
                else 
                {
                    var interfaceType = propertyType.GetInterface("IEnumerable`1");
                    if (interfaceType != null)
                    {
                        var t = interfaceType.GetGenericArguments().FirstOrDefault();
                        if (t.IsSubclassOf(typeof(Entity)))
                        {
                            CreateTable(t, property.Name, pkProperty);
                            continue;
                        }
                    }
                }
                sb.AppendFormat("{0} {1}, ", property.Name, property.PropertyType.GetSqlType());
            }
            sb.AppendFormat("constraint pk_{0} primary key({1}))", tableName, pkProperty.Name);
            Console.WriteLine(sb.ToString());

            Monitor.Enter(obj);
            try
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = sb.ToString();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Monitor.Exit(obj);
            }

            if (TableExisted(tableName))
            {
                Console.WriteLine("create table success.");
            }
        }

        public void CreateTable(Type entityType, string tableName)
        {
            CreateTable(entityType, tableName, null);
        }

        private bool RecordExisted<T>(T entity, string tableName, PropertyInfo pkProperty)
        {
            var keyVal = pkProperty.GetValue(entity, null);
            var sqlCheckExist = string.Format("select count(1) from {0} where {1}=@{1}", tableName, pkProperty.Name);
            var paramKey = new SQLiteParameter("@" + pkProperty.Name, keyVal);
            var cmdCheckExist = connection.CreateCommand();
            cmdCheckExist.CommandText = sqlCheckExist;
            cmdCheckExist.Parameters.Add(paramKey);

            Monitor.Enter(obj);
            try
            {
                var count = (long) cmdCheckExist.ExecuteScalar();
                return count > 0;
            }
            finally
            {
                Monitor.Exit(obj);
            }
        }

        private object Write<T>(T entity, string tableName, Entity parent) where T : Entity
        {
            var entityType = entity.GetType();
            var properties = entityType.GetProperties(BindingFlags.Instance | BindingFlags.Public);
            var pkProperty =
                properties.Where(p => p.Name.EndsWith("id", StringComparison.InvariantCultureIgnoreCase)).First();
            var keyVal = pkProperty.GetValue(entity, null);
            if (RecordExisted(entity, tableName, pkProperty))
            {
                return keyVal;
            }

            var parameterValues = new List<SQLiteParameter>();
            var sb = new StringBuilder();
            sb.AppendFormat("insert into {0}(", tableName);
            if (parent != null)
            {
                PropertyInfo piParentKey = null;
                piParentKey = parent.GetType()
                    .GetProperties()
                    .Where(p => p.Name.EndsWith("Id", StringComparison.InvariantCultureIgnoreCase))
                    .First();
                if (piParentKey != null)
                {
                    var fieldName = GenFieldNameForParentKey(piParentKey);
                    sb.AppendFormat("{0}, ", fieldName);
                    var parameter = new SQLiteParameter("@"+fieldName,piParentKey.PropertyType);
                    parameter.Value = piParentKey.GetValue(parent, null);
                    parameterValues.Add(parameter);
                }
            }

            foreach (PropertyInfo property in properties)
            {
                var propertyType = property.PropertyType;
                var value = property.GetValue(entity, null);
                if (value == null)
                {
                    continue;
                }
                if (property.PropertyType.IsSubclassOf(typeof(ValueObject)))
                {
                    foreach (var pi in property.PropertyType.GetProperties())
                    {
                        var fieldName = string.Format("{0}_{1}", property.Name, pi.Name);
                        sb.AppendFormat("{0}, ", fieldName);
                        var parameter = new SQLiteParameter("@" + fieldName, pi.GetValue(value, null));
                        parameterValues.Add(parameter);
                    }
                    continue;
                }
                else if (propertyType.IsSubclassOf(typeof (Entity)))
                {
                    var val = Write(value as Entity, property.Name, entity);
                    var fieldName = string.Format("{0}Id", property.Name);
                    sb.AppendFormat("{0}, ", fieldName);
                    var parameter = new SQLiteParameter("@" + fieldName, val);
                    parameterValues.Add(parameter);
                    continue;
                }
                else 
                {
                    var interfaceType = propertyType.GetInterface("IEnumerable`1");
                    if (interfaceType != null)
                    {
                        var t = interfaceType.GetGenericArguments().First();
                        if (t.IsSubclassOf(typeof(Entity)))
                        {
                            var items = (IEnumerable<Entity>) value;
                            foreach (Entity item in items)
                            {
                                Write(item, property.Name, entity);
                            }
                            /*var count = propertyType.GetProperty("Count").GetValue(value, null);//取list 的item的个数。
                            for (int i = 0; i < (int) count; i++)
                            {
                                var item = propertyType.GetMethod("get_Item").Invoke(value, new object[] {i}) as Entity; //取item
                                Write(item, property.Name, entity);
                            }*/
                            continue;
                        }
                    }
                }
                sb.AppendFormat("{0}, ", property.Name);
                parameterValues.Add(new SQLiteParameter("@" + property.Name, value));
            }
            sb.Remove(sb.Length - 2, 2).Append(") values(");
            foreach (var parameter in parameterValues)
            {
                sb.AppendFormat("{0}, ", parameter.ParameterName);
            }
            sb.Remove(sb.Length - 2, 2).Append(");");
            Console.WriteLine(sb.ToString());

            Monitor.Enter(obj);
            try
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sb.ToString();
                    cmd.Parameters.AddRange(parameterValues.ToArray());
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                Monitor.Exit(obj);
            }
            return keyVal;
        }

        public void Write<T>(T entity, string tableName) where T : Entity
        {
            Write(entity, tableName, null);
        }
        public void Write<T>(T entity) where T : Entity
        {
            Write(entity, entity.GetType().Name);
        }

        public void Write<T>(IEnumerable<T> entities, string tableName) where T : Entity
        {
            foreach (var entity in entities)
            {
                Write(entity, tableName);
            }
        }

        public void Write<T>(IEnumerable<T> entities) where T : Entity
        {
            Write(entities, typeof(T).Name);
        }

        public SQLiteDataReader Read(string sql, params SQLiteParameter[] parameters)
        {
            Monitor.Enter(obj);
            try
            {
                var cmd = connection.CreateCommand();
                cmd.CommandText = sql;
                cmd.Parameters.AddRange(parameters);
                return cmd.ExecuteReader();
            }
            finally
            {
                Monitor.Exit(obj);
            }
        }

        internal SQLiteDataReader Read(string sql, IEnumerable<KeyValuePair<string, object>> dicts)
        {
            var parameters = new List<SQLiteParameter>();
            foreach (var dict in dicts)
            {
                parameters.Add(new SQLiteParameter(dict.Key, dict.Value));
            }
            return Read(sql, parameters.ToArray());
        }

        public int ExecuteNonQuery(string sql, params SQLiteParameter[] parameters)
        {
            Monitor.Enter(obj);
            try
            {
                using (var cmd = connection.CreateCommand())
                {
                    cmd.CommandText = sql;
                    cmd.Parameters.AddRange(parameters);
                    return cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                Monitor.Exit(obj);                
            }
        }

        public void Dispose()
        {
            if (connection != null)
            {
                connection.Close();
                connection.Dispose();
                connection = null;
            }
        }

    }

    public static class Extensions
    {
        public static string GetSqlType(this Type type)
        {
            if (type == typeof(int))
            {
                return "int";
            }
            else if (type == typeof(string))
            {
                return "varchar(100)";
            }
            else if (type == typeof(DateTime))
            {
                return "datetime";
            }
            else if (type == typeof(decimal) || type == typeof(double) || type == typeof(float))
            {
                return "decimal";
            }
            else if (type == typeof(bool))
            {
                return "int";
            }
            else if (type == typeof(Guid))
            {
                return "guid";
            }
            else
            {
                throw new Exception("不识别的类型："+type.FullName);
            }
        }
    }
}
